package gestores;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import baseDados.Queries;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import dados.Accao;
import dados.Empresa;
import dados.Jogador;
import dados.Registo;
import dados.Stock;
import dados.TipoSugestao;

public class GestorBD {
	private PreparedStatement preparedStatement;
	private Connection connection;

	public GestorBD(Connection connection) {
		this.connection = connection;
	}

	public ArrayList<Stock> selectLastStocks() {
		ArrayList<Stock> stocks = new ArrayList<Stock>();
		try {
			preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.selectLastStocks);
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				Stock s = new Stock(resultSet.getTimestamp("data"),
						resultSet.getString("nome"),
						resultSet.getString("empresa_sigla"),
						resultSet.getDouble("variacao"),
						resultSet.getDouble("cotacao"),
						resultSet.getDouble("valoraltodia"),
						resultSet.getDouble("valorbaixodia"),
						resultSet.getInt("volume"));
				stocks.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return stocks;
	}

	public int insertStock(Stock stock) {
		int row = 0;
		try {
			Timestamp data = new Timestamp(new java.util.Date().getTime());
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(Queries.insertStock);
			preparedStatement.setObject(1, data);
			preparedStatement.setDouble(2, stock.getVariacao());
			preparedStatement.setDouble(3, stock.getCotacao());
			preparedStatement.setDouble(4, stock.getValorAltoDia());
			preparedStatement.setDouble(5, stock.getValorBaixoDia());
			preparedStatement.setInt(6, stock.getVolume());
			preparedStatement.setString(7, stock.getSigla());
			row = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return row;
	}

	// DELETE FROM `stock` WHERE data = 'AAPL'
	public int deleteAllStocks(Timestamp time) {
		int row = 0;
		try {
			preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.deleteStock);
			preparedStatement.setObject(1, time);
			row = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return row;
	}

	public int insertSeveralStocks(ArrayList<Stock> stocks) {
		int rows = 0;
		if (stocks.size() == 0) {
			return rows;
		}
		int it = 0;
		String query = Queries.insertSeveralStocks[0];
		try {
			for (Stock stock : stocks) {
				if (!stocks.get(stocks.size() - 1).equals(stock)) {
					query += Queries.insertSeveralStocks[1];
				} else {
					query += Queries.insertSeveralStocks[2];
				}
			}
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(query);
			for (Stock stock : stocks) {
				Timestamp data = new Timestamp(new java.util.Date().getTime());
				preparedStatement.setObject(++it, data);
				preparedStatement.setDouble(++it, stock.getVariacao());
				preparedStatement.setDouble(++it, stock.getCotacao());
				preparedStatement.setDouble(++it, stock.getValorAltoDia());
				preparedStatement.setDouble(++it, stock.getValorBaixoDia());
				preparedStatement.setInt(++it, stock.getVolume());
				preparedStatement.setString(++it, stock.getSigla());
			}
			rows = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return rows;
	}

	// Jogador
	public Jogador selectJogador(String nome){
		System.out.println("GestorBD - Nome: " + nome);
		Jogador jogador = null;
		try {
			preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.selectUtilizador);
			preparedStatement.setString(1, nome);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				jogador = new Jogador(resultSet.getString("email"), resultSet.getString("nome"), resultSet.getString("password"), resultSet.getDouble("saldo"), resultSet.getDouble("pontos"), resultSet.getBoolean("superuser"));
			}
			if(jogador != null){
				preparedStatement.clearParameters();
				preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.selectAllAccoesFromOneUtilizador);
				preparedStatement.setString(1, jogador.getEmail());
				ResultSet resultSetAccoes = preparedStatement.executeQuery();
				while(resultSetAccoes.next()){
					jogador.getAccoes().add(new Accao(resultSetAccoes.getString("nome"), resultSetAccoes.getString("sigla"), resultSetAccoes.getInt("quantidade"), resultSetAccoes.getDouble("valoraquisicao")));
				}
				preparedStatement.clearParameters();
				preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.selectAllRegistosFromOneUtilizador);
				preparedStatement.setString(1, jogador.getEmail());
				ResultSet resultSetRegistos = preparedStatement.executeQuery();
				while(resultSetRegistos.next()){
					jogador.getHistorial().add(new Registo(resultSetRegistos.getTimestamp("data"), resultSetRegistos.getString("sigla"), resultSetRegistos.getInt("quantidade"), resultSetRegistos.getDouble("valor"), resultSetRegistos.getBoolean("compra_venda")));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return jogador;
	}

	public int deleteAccao(Jogador jogador, Accao accao){
		int row = 0;
		try{
			preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.deleteAccoesFromOneUtilizador);
			preparedStatement.setString(1, jogador.getEmail());
			preparedStatement.setString(2, accao.getSigla());
			row = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return row;
	}

	public int updateJogador(Jogador jogador){
		int rows  = 0;
		int accoesRows = 0;
		int registosRows = 0;
		try {
			preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.updateUtilizador);
			preparedStatement.setDouble(1, jogador.getSaldo());
			preparedStatement.setDouble(2, jogador.getPontos());
			preparedStatement.setString(3, jogador.getEmail());
			rows = preparedStatement.executeUpdate();

			if(!jogador.getAccoes().isEmpty()){
				ArrayList<Accao> accoes = new ArrayList<Accao>();
				preparedStatement.clearParameters();
				preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.selectAllAccoesFromOneUtilizador);
				preparedStatement.setString(1, jogador.getEmail());
				ResultSet resultSetAccoes = preparedStatement.executeQuery();
				while(resultSetAccoes.next()){
					accoes.add(new Accao(resultSetAccoes.getString("nome"), resultSetAccoes.getString("sigla"), resultSetAccoes.getInt("quantidade"), resultSetAccoes.getDouble("valoraquisicao")));
				}
				for (Accao accao : jogador.getAccoes()) {
					//	System.out.println(accoes.get(accoes.indexOf(accao)) + " - " + accao);
					if(!accoes.contains(accao)){
						preparedStatement.clearParameters();
						preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.insertAccoesFromOneUtilizador);
						preparedStatement.setString(1, accao.getNome());
						preparedStatement.setString(2, jogador.getEmail());
						preparedStatement.setString(3, accao.getSigla());
						preparedStatement.setInt(4, accao.getQuantidade());
						preparedStatement.setDouble(5, accao.getValorAquisicao());
						accoesRows += preparedStatement.executeUpdate();
					} else if(accoes.get(accoes.indexOf(accao)).getQuantidade() != accao.getQuantidade()){
						preparedStatement.clearParameters();
						preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.updateAccoesFromOneUtilizador);
						preparedStatement.setInt(1, accao.getQuantidade());
						preparedStatement.setDouble(2, accao.getValorAquisicao());
						preparedStatement.setString(3, jogador.getEmail());
						preparedStatement.setString(4, accao.getNome());
						accoesRows += preparedStatement.executeUpdate();
					} else if(accoes.contains(accao)){
					} else {
						System.out.println("Ja nao existe... Foi Vendida?...");
					}
				}
				ArrayList<Registo> registos = new ArrayList<Registo>();
				preparedStatement.clearParameters();
				preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.selectAllRegistosFromOneUtilizador);
				preparedStatement.setString(1, jogador.getEmail());
				ResultSet resultSetRegistos = preparedStatement.executeQuery();
				while(resultSetRegistos.next()){
					registos.add(new Registo(resultSetRegistos.getTimestamp("data"), resultSetRegistos.getString("sigla"), resultSetRegistos.getInt("quantidade"), resultSetRegistos.getDouble("valor"), resultSetRegistos.getBoolean("compra_venda")));
				}
				for (Registo registo : jogador.getHistorial()) {
					if(!registos.contains(registo)){
						preparedStatement.clearParameters();
						preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.insertRegisto);
						//						Timestamp data = new Timestamp(new java.util.Date().getTime());
						preparedStatement.setObject(1, registo.getTimestamp());
						preparedStatement.setString(2, jogador.getEmail());
						preparedStatement.setString(3, registo.getSigla());
						preparedStatement.setInt(4, registo.getQuantidade());
						preparedStatement.setDouble(5, registo.getValor());
						preparedStatement.setBoolean(6, registo.isCompra_venda());
						registosRows += preparedStatement.executeUpdate();
						//Registar ocorrencia dos agentes
						preparedStatement.clearParameters();
						preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.selectSugestoesFromOnesUtilizador);
						preparedStatement.setString(1, jogador.getEmail());
						ResultSet resultSetSugestoes = preparedStatement.executeQuery();
						int analista = 0, vigarista = 0, transaccoes = 0;
						while(resultSetSugestoes.next()){
							analista = resultSetSugestoes.getInt("analista");
							vigarista = resultSetSugestoes.getInt("vigarista");
							transaccoes = resultSetSugestoes.getInt("transaccoes");
						}
						preparedStatement.clearParameters();
						preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.updateSugestoes);
						if(registo.getTiposugestao() == TipoSugestao.COMPRA_ANALISTA || registo.getTiposugestao() == TipoSugestao.VENDA_ANALISTA ||
								registo.getTiposugestao() == TipoSugestao.VOLUME_ANALISTA){
							preparedStatement.setInt(1, analista + 1);
							preparedStatement.setInt(2, vigarista);
						}else if(registo.getTiposugestao() == TipoSugestao.COMPRA_VIGARISTA || registo.getTiposugestao() == TipoSugestao.VENDA_VIGARISTA){
							preparedStatement.setInt(1, analista);
							preparedStatement.setInt(2, vigarista + 1);
						}else if(registo.getTiposugestao() == TipoSugestao.OSDOIS_COMPRA || registo.getTiposugestao() == TipoSugestao.OSDOIS_VENDA){
							preparedStatement.setInt(1, analista + 1);
							preparedStatement.setInt(2, vigarista + 1);
						}else{
							preparedStatement.setInt(1, analista);
							preparedStatement.setInt(2, vigarista);
						}
						preparedStatement.setInt(3, transaccoes + 1);
						preparedStatement.setString(4, jogador.getEmail());
						preparedStatement.executeUpdate();
					}
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		System.out.println("Guardar Jogador - Affected " + rows + " row(s) + Guardar Accoes - affected " + accoesRows + " row(s) + Guardar Registos - affected " + registosRows + " row(s).");
		return rows;
	}

	// Empresa
	public Empresa selectEmpresas(String sigla){
		Empresa empresa = null;
		try {
			preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.selectEmpresa);
			preparedStatement.setString(1, sigla);
			ResultSet resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				empresa = new Empresa(resultSet.getString("nome"), resultSet.getString("sigla"));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return empresa;
	}

	public ArrayList<Empresa> selectAllEmpresas(){
		ArrayList<Empresa> empresas = new ArrayList<Empresa>();
		try {
			preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.selectAllEmpresas);

			ResultSet resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				Empresa empresa = new Empresa(resultSet.getString("nome"), resultSet.getString("sigla"));
				empresas.add(empresa);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return empresas;
	}

	public int insertEmpresa(Empresa empresa) {
		int row = 0;
		try {
			preparedStatement = (PreparedStatement) connection.prepareStatement(Queries.insertEmpresa);
			preparedStatement.setString(1, empresa.getSigla());
			preparedStatement.setString(2, empresa.getNome());
			row = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return row;
	}

	public int insertSeveralEmpresas(ArrayList<Empresa> empresas){
		int rows = 0;
		if (empresas.size() == 0) {
			return rows;
		}
		int it = 0;
		String query = Queries.insertSeveralEmpresas[0];
		try {
			for (Empresa empresa : empresas) {
				if (!empresas.get(empresas.size() - 1).equals(empresa)) {
					query += Queries.insertSeveralEmpresas[1];
				} else {
					query += Queries.insertSeveralEmpresas[2];
				}
			}
			preparedStatement = (PreparedStatement) connection.prepareStatement(query);
			for (Empresa empresa : empresas) {
				preparedStatement.setString(++it, empresa.getSigla());
				preparedStatement.setString(++it, empresa.getNome());
			}
			rows = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return rows;
	}
}
